package july.android.cakes.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.LinkedList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.sql.*;
import java.io.*;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

@Service
@Transactional
public class STeacherClass {

    @Autowired
    private JdbcTemplate jdbc;
    
    //根据“教师号”获取其所教所有班级
    public List<Map<String, Object>> getMyClass(String tea_id) {
        return this.jdbc.queryForList("select tea2ma.grade,major.nname as m_name,major.id as m_id,course.nname as c_name,course.id as c_id from major,tea2ma,course,cou2tea where major.id=tea2ma.ma_id and tea2ma.tea_id=cou2tea.tea_id and cou2tea.cou_id=course.id and cou2tea.tea_id=?", tea_id);
    }

    //根据“课程号、专业号、年级”获取组消息
    public List<Map<String, Object>> getMyClassMessage(String c_id, String m_id, String grade) {
        List<Map<String, Object>> temp = jdbc.queryForList("select chat,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date,pull from cou_chat where cou_id=? and major_id=? and grade = ?", c_id, m_id, grade);
        List formal = new LinkedList();
        for (Map<String, Object> item : temp) {
            String pull = item.get("pull").toString();
            if (pull.length() == 13) {
                item.putAll(jdbc.queryForMap("select nname,icon from student where id=?", pull));
            } else {
                item.putAll(jdbc.queryForMap("select nname,icon from teacher where id=?", pull));
            }
            formal.add(item);
        }
        return formal;
    }

    //根据传入的教师工号获取其加入的讨论组
    public List<Map<String, Object>> getTeacherSinDiscussion(String tea_id) {
        return this.jdbc.queryForList("select discussion_group.id,discussion_group.nname from discussion_group,dis2tea where id=g_id and tea_id=?", tea_id);
    }

    //根据“讨论组号”获取讨论组消息
    public List<Map<String, Object>> getMyDisMessage(String id) {
        List<Map<String, Object>> temp = jdbc.queryForList("select chat,pull,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date from discussion_group,dis_chat where  dis_chat.g_id=discussion_group.id and discussion_group.id=?", id);
        List formal = new LinkedList();
        for (Map<String, Object> item : temp) {
            String pull = item.get("pull").toString();
            if (pull.length() == 13) {
                item.putAll(jdbc.queryForMap("select nname,icon from student where id=?", pull));
            } else {
                item.putAll(jdbc.queryForMap("select nname,icon from teacher where id=?", pull));
            }
            formal.add(item);
        }
        return formal;
    }

    //把“讨论组号，发送人，信息”插入dis_chat
    public void addDisChat(String g_id, String pull, String chat) {
        this.jdbc.update("insert into dis_chat(g_id,pull,chat) values(?,?,?)", g_id, pull, chat);
    }

    //根据“教师号”获取其教的全部学生
    public List<Map<String, Object>> getMyStudent(String tea_id) {
        return this.jdbc.queryForList("select student.id,student.nname as s_name,student.grade,major.nname as name from student,tea2ma,major where tea2ma.ma_id=student.ma_id and student.ma_id=major.id and tea2ma.tea_id=?", tea_id);

    }

    //获取全部教师信息
    public List<Map<String, Object>> allTeacher() {
        return this.jdbc.queryForList("select teacher.nname as s_name,teacher.id,department.nname as name from teacher,department where teacher.de_id=department.id");
    }

    //获取建讨论组时所需信息
    public List<Map<String, Object>> groupNeed(String tea_id) {
        List<Map<String, Object>> list = getMyStudent(tea_id);
        list.addAll(allTeacher());
        return list;
    }

    //创建讨论组
    public void createDis(String[] data) {
        boolean only = true;
        int id=0;
        for (String item : data) {
            if (only) {
                String nname = item;
                String sql = "insert into discussion_group(nname) values(?)";
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbc.update(new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(sql, new String[]{"nname"});
                        ps.setString(1, nname);
                        return ps;
                    }
                }, keyHolder);
                id = keyHolder.getKey().intValue();
                only=false;
                continue;
            }
            if (item.length()==13){
                this.jdbc.update("insert into dis2stu(g_id,stu_id) values(?,?)",id,item);
            }else{
                this.jdbc.update("insert into dis2tea(g_id,tea_id) values(?,?)",id,item);
            }
        }   
    }
    
        //根据“教师号”获取某个教师上传的全部课件
    public List<Map<String,Object>>getallfiles(String tea_id){
        return this.jdbc.queryForList("select id,nname,url,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date,remark from files,tea2files where tea2files.f_id=files.id and tea_id=?",tea_id);
        
    }
    
    //根据“课件号”获取授权的年级专业
    public List<Map<String,Object>>getFileMaster(String f_id){
        return this.jdbc.queryForList("select nname from major2files,major where major2files.ma_id=major.id and f_id=?",f_id);
    }

    public void delAfile(int f_id){
        this.jdbc.update("delete from major2files where f_id =?",f_id);
        this.jdbc.update("delete from tea2files where f_id =?",f_id);
        this.jdbc.update("delete from files where id =?",f_id);
    }
    /**
     * ***********************************************************************************************
     */
    //向数据库插入上传的课件的详细信息
    public void addfiles(String nname, String url, String tea_id, String major, String grade) {
        String major_id = getmajorid(major);
        this.jdbc.update("insert into files values(?,?,?,?)", nname, url, Integer.parseInt(major_id), grade);
        String files_id = String.valueOf((this.jdbc.queryForMap("select id from files where url=?", url)).get("id"));
        this.jdbc.update("insert into tea2files(tea_id,f_id) values(?,?)", tea_id, Integer.parseInt(files_id));
    }

    //根据教师号，专业名，年级获取某个教师上传的课件信息
    public List<Map<String,Object>>getsinfiles(String tea_id,String major_id,String grade){
        return this.jdbc.queryForList("select id,nname,url,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date,remark from files,major2files,tea2files where tea2files.f_id=major2files.f_id and major2files.f_id=files.id and tea_id=? and major_id=? and stu_grade=?",tea_id,major_id,grade);
    }
    

    
    //根据专业名返回专业号
    public String getmajorid(String major) {
        return String.valueOf((this.jdbc.queryForMap("select id from major where nname=?", major)).get("id"));
    }
    //根据传入的教师工号及其针对某个专业发布的课程消息内容插入数据库

    public void addteachercoursemessage(String id, String grade, String m_id, String c_id, String chat) {
        this.jdbc.update("insert into cou_chat(cou_id,pull,chat,major_id,grade) values(?,?,?,?,?)", c_id, id, chat, m_id, grade);
    }

    //根据传入的教师工号及其讨论组发布的消息内容插入数据库，前台应保存讨论组号
    public void addteachergroupmessage(String tea_id, String dis_id, String chat) {
        this.jdbc.update("insert into dis_chat values(?,?,?)", Integer.parseInt(dis_id), tea_id, chat);
    }

    //若前台没有保存讨论组号，可通过以下方法获得讨论组号
    public String getdis_id(String dis_name) {
        return (String) (this.jdbc.queryForMap("select id from discussion_group where nname=?", dis_name)).get("id");
    }

    //根据传来的教师工号获得其所教的年级专业（方法一）
    public List<Map<String, Object>> getgrade_major(String id) {
        return this.jdbc.queryForList("select grade,major.nname,major.id as m_id from major,tea2ma where major.id=tea2ma.ma_id and tea_id=?", id);
    }

    //根据传来的教师工号获得其所教的年级专业（方法二）
    public List<Map<String, Object>> getgrade_major2(String id) {
        return this.jdbc.queryForList("select distinct grade,major.nname from cou2tea,cou2stu,student,major where cou2tea.cou_id=cou2stu.cou_id and cou2tea.tea_id=? and student.id=cou2stu.stu_id and major.id=student.ma_id", id);
    }

    //获得根据学号排列的学生信息
    public List<Map<String, Object>> getidsort(String grade, String major) {
        return this.jdbc.queryForList("select student.id,student.nname,major.nname from student,major where student.ma_id=major.id and major.nname=? and grade=? order by id asc", major, grade);
    }

    //获得根据姓氏排列的学生信息
    public List<Map<String, Object>> getnamesort(String grade, String major) {
        return this.jdbc.queryForList("select student.id,student.nname,major.nname from student,major where student.ma_id=major.id and major.nname=? and grade=? order by convert(stu_name USING gbk)collate gbk_chinese_ci asc", major, grade);
    }

    //根据传入的教师工号获取其所教年级专业及通知消息
    public List<Map<String, Object>> getpostmessage(String tea_id) {
        return this.jdbc.queryForList("select tea2ma.grade,major.nname,pull,chat,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date from major,tea2ma,cou_chat,cou2tea where major.id=tea2ma.ma_id and tea2ma.tea_id=cou2tea.tea_id and tea2ma.grade=cou_chat.grade and major.id=cou_chat.major_id and cou_chat.cou_id=cou2tea.cou_id and cou2tea.tea_id=?", tea_id);
    }

    //根据传入的教师工号判断其是否加入讨论组，若加入则显示讨论组名字及讨论组消息
    public List<Map<String, Object>> getteacherdiscussion(String tea_id) {
        return this.jdbc.queryForList("select discussion_group.id,nname,chat,pull,DATE_FORMAT(ttime,'%y-%m-%d %H:%i:%s')as date from dis2tea,discussion_group,dis_chat where dis2tea.g_id=discussion_group.id and dis_chat.g_id=discussion_group.id and tea_id=?", tea_id);
    }

    //插入讨论组名字（必须是数据库中没有的，否则提醒用户该讨论组名字已存在），并返回讨论组的dis_id，前台应以变量存之
    public String adddiscussionname(String name) {
        List<Map<String, Object>> m = this.jdbc.queryForList("select * from discussion_group");
        for (int i = 0; i < m.size(); i++) {
            if (m.get(i).get("nname").equals(name)) {
                return "讨论组名字已存在";
            }
        }
        this.jdbc.update("insert into discussion_group(nname) values(?)", name);
        return String.valueOf((this.jdbc.queryForMap("select id from discussion_group where nname=?", name)).get("id"));
    }

    //根据传入的教师工号和讨论组的id往数据库表dis2tea中插入数据
    public void adddis_tea(String dis_id, String tea_id) {
        this.jdbc.update("insert into dis2tea（g_id,tea_id) values(?,?)", Integer.parseInt(dis_id), tea_id);
    }

    //根据传入的学生学号和讨论组的id往数据库表dis2stu中插入数据
    public void adddis_stu(String dis_id, String stu_id) {
        this.jdbc.update("insert into dis2stu（g_id,stu_id) values(?,?)", Integer.parseInt(dis_id), stu_id);
    }

    //根据学生学号进行搜索
    public List<Map<String, Object>> searchbyid(String stu_id) {
        return this.jdbc.queryForList("select student.id,student.nname,major.nname from student,major where student.ma_id=major.id and student.id=?", stu_id);
    }

    //根据学生姓名进行搜索
    public List<Map<String, Object>> searchbyname(String stu_name) {
        return this.jdbc.queryForList("select student.id,student.nname,major.nname from student,major where student.ma_id=major.id and student.nname=?", stu_name);
    }

    //根据教师工号进行搜索
    public List<Map<String, Object>> searchid(String tea_id) {
        return this.jdbc.queryForList("select id,nname,tel,email,address from teacher,tea_contacts where teacher.id=tea_contacts.tea_id and id=?", tea_id);
    }

    //根据教师姓名进行搜索
    public List<Map<String, Object>> searchname(String tea_name) {
        return this.jdbc.queryForList("select id,nname,tel,email,address from teacher,tea_contacts where teacher.id=tea_contacts.tea_id and nname=?", tea_name);
    }
}
